using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Globalization;
using System.Collections.Generic;
using System.Xml.Serialization;
using System.IO;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using AdHocReports.Properties;

namespace AdHocReports
{
    public partial class showReport : BasePage
    {
        private enum AggregateType
        {
            Avg,
            Count,
            Max,
            Min,
            Sum
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Global.Expired == false)
            {
                if (ReportDefinition.ReportID == -1)
                {
                    lblReport.Text = "Viewing New Report";
                    lblReport.ForeColor = Color.Black;
                }
                else
                {
                    lblReport.Text = "Viewing Saved Report: " + ReportDefinition.ReportName;
                    lblReport.ForeColor = Color.Black;
                    txtReportName.Visible = false;
                    cmdSave.ImageUrl = "images/save_disabled32x32.gif";
                }

                if ((ReportDefinition.SelectColumns.Count > 0) && (ReportDefinition.View.Length > 0))
                {
                    // Create the data adapter
                    System.Data.OleDb.OleDbDataAdapter oleDA = new System.Data.OleDb.OleDbDataAdapter(ReportDefinition.SQL, ReportDefinition.ConnectionString);

                    // Create and fill the data table
                    System.Data.DataTable dt = new System.Data.DataTable();
                    oleDA.Fill(dt);

                    /***********
                     * Headers *
                     ***********/
                    using (System.Web.UI.WebControls.TableRow trHeader = new System.Web.UI.WebControls.TableRow())
                    {
                        trHeader.CssClass = "trHeader";

                        // Loop through the selected groups
                        for (int i = 0; i < ReportDefinition.GroupColumns.Count; i++)
                        {
                            // Create the cell for holding the group column name
                            System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                            // The CSS identifier
                            tc.CssClass = "tcGroupCol";
                            // Set the text
                            tc.Text = ReportDefinition.GroupColumns[i];
                            // Add the cell
                            trHeader.Cells.Add(tc);
                        }

                        // Loop through the regular columns
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            // If the column is NOT part of the group by
                            if (ReportDefinition.GroupColumns.IndexOf(dt.Columns[i].ColumnName) == -1)
                            {
                                // Show the column name
                                System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                                // CSS identifier
                                tc.CssClass = "tcSelCol";
                                // Set the text
                                tc.Text = dt.Columns[i].ColumnName;
                                // Add the cell
                                trHeader.Cells.Add(tc);
                            }
                            else
                            {
                                // Show the column name
                                System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                                // CSS identifier
                                tc.CssClass = "tcSelCol";
                                // Hide "SELECT" cells that are in the "GROUP"
                                tc.Visible = false;
                                // Add the cell
                                trHeader.Cells.Add(tc);
                            }
                        }

                        // Add the header row
                        tabReport.Rows.Add(trHeader);
                    }
                    /***************
                     * End Headers *
                     ***************/

                    /***********************
                     * Data Row Processing *
                     ***********************/
                    // Loop through the data rows
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        // Loop through the group columns
                        for (int j = 0; j < ReportDefinition.GroupColumns.Count; j++)
                        {
                            // Create the row
                            using (System.Web.UI.WebControls.TableRow trGroup = new System.Web.UI.WebControls.TableRow())
                            {
                                // CSS identifier
                                trGroup.CssClass = "trGroup";

                                // Get the group value
                                string sGroupVal = dt.Rows[i][ReportDefinition.GroupColumns[j]].ToString();
                                if (sGroupVal.Length == 0)
                                {
                                    // Set to the equivalent HTML "blank"
                                    sGroupVal = "&nbsp;";
                                }

                                // Check if the previous group doesn't match the current group
                                // (i.e., detect a group change)
                                if ((i == 0) || (object.Equals(dt.Rows[i - 1][ReportDefinition.GroupColumns[j]], dt.Rows[i][ReportDefinition.GroupColumns[j]]) == false))
                                {
                                    // If this is the first pass on groups
                                    if (j == 0)
                                    {
                                        // Create and blank row and add it to the table
                                        using (System.Web.UI.WebControls.TableRow tr = new System.Web.UI.WebControls.TableRow())
                                        {
                                            // CSS identifier
                                            tr.CssClass = "trGroupSpacer";
                                            using (System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell())
                                            {
                                                // CSS identifier
                                                tc.CssClass = "tcGroupSpacer";
                                                // Set to HTML blank
                                                tc.Text = "&nbsp;";
                                                // Span the whole length
                                                tc.ColumnSpan = ReportDefinition.SelectColumns.Count;
                                                // Add the cell
                                                tr.Cells.Add(tc);
                                            }
                                            // Add the row
                                            tabReport.Rows.Add(tr);
                                        }
                                    }

                                    // Add blank cells before the displaying this
                                    // group column value (to align the group value
                                    // with the group column)
                                    for (int k = 0; k < j; k++)
                                    {
                                        // Create the cell
                                        using (System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell())
                                        {
                                            // CSS identifier
                                            tc.CssClass = "tcPreGroupSpacer";
                                            // HTML blank
                                            tc.Text = "&nbsp;";
                                            // Add the cell
                                            trGroup.Cells.Add(tc);
                                        }
                                    }

                                    // Create the cell to show the group value
                                    using (System.Web.UI.WebControls.TableCell tcGroupVal = new System.Web.UI.WebControls.TableCell())
                                    {
                                        // CSS identifier
                                        tcGroupVal.CssClass = "tcGroupVal";
                                        // Calculate and set the colspan
                                        tcGroupVal.ColumnSpan = (ReportDefinition.SelectColumns.Count - j);
                                        // Set the text
                                        tcGroupVal.Text = sGroupVal;
                                        // Add the cell
                                        trGroup.Cells.Add(tcGroupVal);
                                    }
                                }

                                // If the group row has cells
                                if (trGroup.Cells.Count > 0)
                                {
                                    // Add the row to the table
                                    tabReport.Rows.Add(trGroup);
                                }
                            }
                        }

                        // Add a new result row
                        System.Web.UI.WebControls.TableRow trData = new System.Web.UI.WebControls.TableRow();
                        // CSS identifier
                        trData.CssClass = "trData";

                        // Loop through the group columns
                        for (int j = 0; j < ReportDefinition.GroupColumns.Count; j++)
                        {
                            // Add blank cells for each group column
                            System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                            // CSS identifier
                            tc.CssClass = "tcPreDataSpacer";
                            // HTML Blank
                            tc.Text = "&nbsp;";
                            // Add the cell
                            trData.Cells.Add(tc);
                        }

                        // Loop through the data columns
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            // If the current column is NOT part of the GROUP BY
                            if (ReportDefinition.GroupColumns.IndexOf(dt.Columns[j].ColumnName) == -1)
                            {
                                // Get the column value, and display it
                                System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                                // CSS identifier
                                tc.CssClass = "tcDataVal";
                                // Set the text
                                tc.Text = dt.Rows[i][j].ToString().Trim();
                                // If there's no data
                                if (tc.Text.Length == 0)
                                {
                                    // Add a non-breaking space
                                    tc.Text = "&nbsp;";
                                }
                                // Add the cell to the data row
                                trData.Cells.Add(tc);
                            }
                            else
                            {
                                // Get the column value, and display it
                                System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                                // CSS identifier
                                tc.CssClass = "tcDataVal";
                                // Hide "SELECT" cells that are in the "GROUP"
                                tc.Visible = false;
                                // Add the cell to the data row
                                trData.Cells.Add(tc);
                            }
                        }

                        // If the data row has cells
                        if (trData.Cells.Count > 0)
                        {
                            // Add the row to the report
                            tabReport.Rows.Add(trData);
                        }

                        List<TableRow> aggregateRows = new List<TableRow>();

                        // If we're not on the last row
                        if (i != (dt.Rows.Count - 1))
                        {
                            bool doGroupAggregates = false;

                            for (int j = 0; j < ReportDefinition.GroupColumns.Count; j++)
                            {
                                if ((dt.Rows[i][ReportDefinition.GroupColumns[j]].ToString() != dt.Rows[i + 1][ReportDefinition.GroupColumns[j]].ToString()) || (doGroupAggregates == true))
                                {
                                    doGroupAggregates = true;

                                    TableRow tr = CalculateGroupAggregates(i, j, dt);
                                    if (tr != null)
                                    {
                                        aggregateRows.Add(tr);
                                    }
                                }
                            }
                        }
                        else
                        {
                            // Loop through all groups, as all groups have ended
                            for (int j = 0; j < ReportDefinition.GroupColumns.Count; j++)
                            {
                                TableRow tr = CalculateGroupAggregates(i, j, dt);
                                if (tr != null)
                                {
                                    aggregateRows.Add(tr);
                                }
                            }
                        }

                        for (int j = (aggregateRows.Count - 1); j >= 0; j--)
                        {
                            tabReport.Rows.Add(aggregateRows[j]);
                        }
                    }
                    /***************************
                     * End Data Row Processing *
                     ***************************/

                    // Free the data adapter
                    oleDA.Dispose();
                    oleDA = null;

                    // Free the data table
                    dt.Dispose();
                    dt = null;
                }
            }
        }

        protected void cmdSave_Click(object sender, ImageClickEventArgs e)
        {
            XmlSerializer xml = new XmlSerializer(typeof(ReportDefinition));
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);

            ReportDefinition.ReportName = this.txtReportName.Text;

            xml.Serialize(writer, ReportDefinition);

            using (SqlConnection conn = new SqlConnection(Settings.Default.SavedReports))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    if (ReportDefinition.ReportID != -1)
                    {
                        comm.CommandText = "UPDATE Reports SET ReportName = @reportName, ReportDefinition = @reportDefinition WHERE ReportID = @reportId";
                        comm.Parameters.Add(new SqlParameter("reportName", SqlDbType.VarChar, 0, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, ReportDefinition.ReportName));
                        comm.Parameters.Add(new SqlParameter("reportDefinition", SqlDbType.Xml, 0, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, sb.ToString()));
                        comm.Parameters.Add(new SqlParameter("reportId", SqlDbType.Int, 0, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, ReportDefinition.ReportID));
                    }
                    else
                    {
                        comm.CommandText = "INSERT INTO Reports (ReportName, ReportDefinition) VALUES (@reportName, @reportDefinition)";
                        comm.Parameters.Add(new SqlParameter("reportName", SqlDbType.VarChar, 0, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, ReportDefinition.ReportName));
                        comm.Parameters.Add(new SqlParameter("reportDefinition", SqlDbType.Xml, 0, ParameterDirection.Input, false, 0, 0, string.Empty, DataRowVersion.Default, sb.ToString()));
                    }
                    comm.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        protected void cmdPrint_Click(object sender, ImageClickEventArgs e)
        {
            this.tabMenu.Visible = false;
            this.ClientScript.RegisterClientScriptBlock(this.GetType(), "PrintHandler", "<script>window.print();</script>");
            this.ClientScript.RegisterClientScriptBlock(this.GetType(), "HistoryHandler", "<script>function window.onafterprint(){history.back();}</script>");
        }

        protected void cmdExport_Click(object sender, ImageClickEventArgs e)
        {
            ExcelExport.Export(Server.MapPath("~") + @"\rt\report.xls", Server.MapPath("~") + @"\default.css", this.tabReport, this.Response);
        }

        private TableRow CalculateGroupAggregates(int iGroupRow, int iGroupCol, DataTable dt)
        {
            if ((ReportDefinition.SumColumns.Count > 0) || (ReportDefinition.AvgColumns.Count > 0) || (ReportDefinition.MinColumns.Count > 0) || (ReportDefinition.MaxColumns.Count > 0) || (ReportDefinition.CountColumns.Count > 0))
            {
                TableRow tr = new TableRow();

                for (int i = 0; i < (ReportDefinition.SelectColumns.Count + ReportDefinition.GroupColumns.Count); i++)
                {
                    // Add a blank cell for each column
                    System.Web.UI.WebControls.TableCell tc = new System.Web.UI.WebControls.TableCell();
                    tc.CssClass = "tcGroupTotals";
                    // During iteration, if we are past the "GROUP" columns and if the current "SELECT" column
                    // is in the "GROUP"
                    if ((i >= ReportDefinition.GroupColumns.Count) && (ReportDefinition.GroupColumns.IndexOf(ReportDefinition.SelectColumns[i - ReportDefinition.GroupColumns.Count]) != -1))
                    {
                        // Hide this cell
                        tc.Visible = false;
                    }
                    tr.Cells.Add(tc);
                }

                tr.Cells[iGroupCol].Text = Settings.Default.PrefixAggregate;

                CalculateGroupAggregate(AggregateType.Sum, ReportDefinition.SumColumns, iGroupRow, iGroupCol, dt, tr);
                CalculateGroupAggregate(AggregateType.Avg, ReportDefinition.AvgColumns, iGroupRow, iGroupCol, dt, tr);
                CalculateGroupAggregate(AggregateType.Min, ReportDefinition.MinColumns, iGroupRow, iGroupCol, dt, tr);
                CalculateGroupAggregate(AggregateType.Max, ReportDefinition.MaxColumns, iGroupRow, iGroupCol, dt, tr);
                CalculateGroupAggregate(AggregateType.Count, ReportDefinition.CountColumns, iGroupRow, iGroupCol, dt, tr);

                return tr;
            }

            return null;
        }

        private void CalculateGroupAggregate(AggregateType type, List<string> columns, int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            // Cell prefix for aggregation result
            string prefix = string.Empty;

            // Groups to be actually used in aggregation
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);

            // Number of groups to remove, based off group position
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            // Remove groups that cannot be used in this aggregation (because of position)
            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns to aggregate
            for (int i = 0; i < columns.Count; i++)
            {
                // Find the position of the aggregate column in the select columns
                int aggregateCellLocation = ReportDefinition.SelectColumns.IndexOf(columns[i]);

                // If it was found
                if (aggregateCellLocation != -1)
                {
                    // Start building the aggregate SQL
                    string sql = "SELECT ISNULL(";

                    // Determine the aggregate type
                    switch (type)
                    {
                        case AggregateType.Avg:
                            sql += "AVG";
                            prefix = Settings.Default.PrefixAvg;
                            break;
                        case AggregateType.Count:
                            sql += "COUNT";
                            prefix = Settings.Default.PrefixCount;
                            break;
                        case AggregateType.Max:
                            sql += "MAX";
                            prefix = Settings.Default.PrefixMax;
                            break;
                        case AggregateType.Min:
                            sql += "MIN";
                            prefix = Settings.Default.PrefixMin;
                            break;
                        case AggregateType.Sum:
                            sql += "SUM";
                            prefix = Settings.Default.PrefixSum;
                            break;
                    }

                    // Continue building the aggregate SQL
                    sql += "(\"" + columns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + aggregateCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", prefix, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }

        private void CalculateSumAggregates(int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns we'll aggregate
            for (int i = 0; i < ReportDefinition.SumColumns.Count; i++)
            {
                // Extract the aggregate column location in the "SELECT" column list
                int sumCellLocation = ReportDefinition.SelectColumns.IndexOf(ReportDefinition.SumColumns[i]);

                // Check if the aggregate column exists as a "SELECT" column (so we know where to place the result in the data table)
                if (sumCellLocation != -1)
                {
                    // Build the aggregate SQL
                    string sql = "SELECT ISNULL(SUM(\"" + ReportDefinition.SumColumns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + sumCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", Settings.Default.PrefixSum, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }

        private void CalculateAvgAggregates(int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns we'll aggregate
            for (int i = 0; i < ReportDefinition.AvgColumns.Count; i++)
            {
                // Extract the aggregate column location in the "SELECT" column list
                int avgCellLocation = ReportDefinition.SelectColumns.IndexOf(ReportDefinition.AvgColumns[i]);

                // Check if the aggregate column exists as a "SELECT" column (so we know where to place the result in the data table)
                if (avgCellLocation != -1)
                {
                    // Build the aggregate SQL
                    string sql = "SELECT ISNULL(AVG(\"" + ReportDefinition.AvgColumns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + avgCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", Settings.Default.PrefixAvg, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }

        private void CalculateMinAggregates(int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns we'll aggregate
            for (int i = 0; i < ReportDefinition.MinColumns.Count; i++)
            {
                // Extract the aggregate column location in the "SELECT" column list
                int minCellLocation = ReportDefinition.SelectColumns.IndexOf(ReportDefinition.MinColumns[i]);

                // Check if the aggregate column exists as a "SELECT" column (so we know where to place the result in the data table)
                if (minCellLocation != -1)
                {
                    // Build the aggregate SQL
                    string sql = "SELECT ISNULL(MIN(\"" + ReportDefinition.MinColumns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + minCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", Settings.Default.PrefixMin, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }

        private void CalculateMaxAggregates(int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns we'll aggregate
            for (int i = 0; i < ReportDefinition.MaxColumns.Count; i++)
            {
                // Extract the aggregate column location in the "SELECT" column list
                int maxCellLocation = ReportDefinition.SelectColumns.IndexOf(ReportDefinition.MaxColumns[i]);

                // Check if the aggregate column exists as a "SELECT" column (so we know where to place the result in the data table)
                if (maxCellLocation != -1)
                {
                    // Build the aggregate SQL
                    string sql = "SELECT ISNULL(MAX(\"" + ReportDefinition.MaxColumns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + maxCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", Settings.Default.PrefixMax, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }

        private void CalculateCountAggregates(int iGroupRow, int iGroupCol, DataTable dt, TableRow aggregateRow)
        {
            List<string> tempGroups = new List<string>(ReportDefinition.GroupColumns);
            int columnsToRemove = ((ReportDefinition.GroupColumns.Count - 1) - iGroupCol);

            for (int i = 0; i < columnsToRemove; i++)
            {
                tempGroups.RemoveAt(tempGroups.Count - 1);
            }

            // Loop through the columns we'll aggregate
            for (int i = 0; i < ReportDefinition.CountColumns.Count; i++)
            {
                // Extract the aggregate column location in the "SELECT" column list
                int countCellLocation = ReportDefinition.SelectColumns.IndexOf(ReportDefinition.CountColumns[i]);

                // Check if the aggregate column exists as a "SELECT" column (so we know where to place the result in the data table)
                if (countCellLocation != -1)
                {
                    // Build the aggregate SQL
                    string sql = "SELECT ISNULL(COUNT(\"" + ReportDefinition.CountColumns[i] + "\"), 0) FROM \"" + ReportDefinition.View + "\" WHERE ";

                    // Loop through the group columns to build an accurate group by clause
                    for (int j = 0; j < tempGroups.Count; j++)
                    {
                        // "WHERE groupColumn = "
                        sql += tempGroups[j] + " = ";

                        // If the value is not numeric
                        if (Global.IsNumeric(dt.Rows[iGroupRow][tempGroups[j]].ToString()) == false)
                        {
                            // Append the value with quotes
                            sql += "'" + dt.Rows[iGroupRow][tempGroups[j]].ToString() + "'";
                        }
                        else
                        {
                            // Otherwise, just append the raw value
                            sql += dt.Rows[iGroupRow][tempGroups[j]].ToString();
                        }

                        // Prepare the SQL for the next group WHERE condition
                        sql += " AND ";
                    }

                    // Append the GROUP BY clause
                    sql = sql.Substring(0, sql.Length - 5) + " GROUP BY ";

                    // Append the groups to the GROUP BY clause
                    sql += "\"" + string.Join("\", \"", tempGroups.ToArray()) + "\"";

                    // Establish a new connection object
                    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(ReportDefinition.ConnectionString);

                    // New command object
                    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);

                    // Open the connection
                    connection.Open();

                    // Display the value
                    aggregateRow.Cells[ReportDefinition.GroupColumns.Count + countCellLocation].Text = string.Format(CultureInfo.CurrentUICulture, "{0}{1}", Settings.Default.PrefixCount, command.ExecuteScalar());

                    // Close the connection
                    connection.Close();
                }
            }
        }
    }
}